from numpy import array
import pandas as pd
import numpy as np
import cvxpy as cp
max_constant = 1000000
tmpl_no = '202309_01'
cog_dest = '发电'
#读数据
data_meizhong = pd.read_excel('炼焦煤煤种.xlsx')
data_meizhong.columns = data_meizhong.columns.str.upper()
data_bili = pd.read_excel('炼焦煤比例限制.xlsx')
data_bili.columns = data_bili.columns.str.upper()
data_meizhi = pd.read_excel('炼焦煤煤质.xlsx')
data_meizhi.columns = data_meizhi.columns.str.upper()
data_shangxiaxian = pd.read_excel('炼焦煤煤质上下限.xlsx')
data_shangxiaxian.columns = data_shangxiaxian.columns.str.upper()
#去除资源量为0的品种，减少变量
#月初库存
data_meizhong.INIT_INV.fillna(0, inplace=True)
#可供资源
data_meizhong.INV_WT.fillna(0, inplace=True)
#资源量
data_meizhong.RESOURCE_WT.fillna(0, inplace=True)
#计划使用量
data_meizhong.PLAN_WT.fillna(0, inplace=True)
def __cal_max_wt(x):
    rst = max(x.RESOURCE_WT, x.PLAN_WT)
    return rst
data_meizhong['MAX_WT'] = data_meizhong.apply(lambda x: __cal_max_wt(x), axis=1)
data_meizhong = data_meizhong[(data_meizhong['MAX_WT'] > 0)]
data_meizhong = data_meizhong.reset_index(drop=True)
# ganmeiliang = data_meizhong['PLAN_WT'].sum()
peihemei_wt = data_meizhong['PLAN_WT'].sum()
#对煤种生成唯一编码mark
#维度上只考虑VAR性状，SOURCE来源
#写死维度种类对应关系？
data_mark = data_meizhong.copy()
def __cal_rank_xingzhuang(x):
    if x.VAR == '主焦':
        rst = 1
    elif x.VAR == '肥煤':
        rst = 2
    elif x.VAR == '1/3焦':
        rst = 3
    elif x.VAR == '气煤':
        rst = 4
    elif x.VAR == '瘦煤':
        rst = 5
    else:
        rst = 6
    return rst
data_mark['xingzhuang_rank'] = data_mark.apply(lambda x: __cal_rank_xingzhuang(x), axis=1)
def __cal_rank_laiyuan(x):
    if x.SOURCE == '进口':
        rst = 1
    elif x.SOURCE == '大矿长协':
        rst = 2
    elif x.SOURCE == '大矿市场':
        rst = 3
    elif x.SOURCE == '地方矿':
        rst = 4
    else:
        rst = 5
    return rst
data_mark['laiyuan_rank'] = data_mark.apply(lambda x: __cal_rank_laiyuan(x), axis=1)
def __cal_rank_group(x):
    rst = str(x.xingzhuang_rank) + '_' + str(x.laiyuan_rank)
    return rst
data_mark['group'] = data_mark.apply(lambda x: __cal_rank_group(x), axis=1)
data_mark = data_mark.reset_index(drop=False)
data_mark.rename(columns={'index': 'index_old'}, inplace=True)

data_mark['rank0'] = data_mark['index_old'].groupby(data_mark['group']).rank()
data_mark['pinming_rank'] = data_mark['rank0'].astype(int)
def __cal_rank_mark(x):
    rst = str(x.group) + '_' + str(x.pinming_rank)
    return rst
data_mark['mark'] = data_mark.apply(lambda x: __cal_rank_mark(x), axis=1)
data_mark.drop(['rank0'], axis=1, inplace=True)
# 统计个数，做个var_df以便后续对应
var_list = ['主焦', '肥煤', '1/3焦', '气煤', '瘦煤']
source_list = ['进口', '大矿长协', '大矿市场', '地方矿']
var_df = pd.DataFrame(columns=['VAR', 'SOURCE', 'group', 'count'])
dict = {}
for i in range(1, len(var_list) + 1):
    for j in range(1, len(source_list) + 1):
        df = data_mark[
            (data_mark['VAR'] == var_list[i - 1]) & (data_mark['SOURCE'] == source_list[j - 1])]
        row_count = df.shape[0]
        # print("行数：", row_count)
        dict['VAR'] = var_list[i - 1]
        dict['SOURCE'] = source_list[j - 1]
        dict['group'] = str(i) + '_' + str(j)
        dict['count'] = row_count
        new_row = pd.Series(dict)
        var_df = var_df.append(new_row, ignore_index=True)
#生成动态参数
#资源量，分配比例上下限
#资源量，按照品名的
data_ziyuanliang_1 = data_mark[['PROD_CODE', 'INIT_INV', 'INV_WT', 'RESOURCE_WT', 'MERGE_TAG', 'MAX_WT', 'mark']]
data_ziyuanliang_1.RESOURCE_WT.fillna(0, inplace=True)
for index, row in data_ziyuanliang_1.iterrows():
    exec('ziyuanliang_{} ={}'.format(row['mark'], row['MAX_WT']))
#资源量，合并单元格的
merge_num = data_mark["MERGE_TAG"].max()
print(merge_num)
for i in range(1, int(merge_num)+1):
    print(i)
    data_ziyuanliang_2 = data_ziyuanliang_1[(data_ziyuanliang_1['MERGE_TAG'] == i)]
    data_ziyuanliang_2 = data_ziyuanliang_2.reset_index(drop=True)
    tmp_merge_ziyuanliang = data_ziyuanliang_2.loc[0]['INV_WT']
    exec('merge_ziyuanliang_{} ={}'.format(i, tmp_merge_ziyuanliang))
#分配比例上下限，按照品名的
data_bili.rename(columns={'MAX_VALUE': 'UL'}, inplace=True)
data_bili.rename(columns={'MIN_VALUE': 'LL'}, inplace=True)
data_bili.UL.fillna(100, inplace=True)
data_bili.LL.fillna(0, inplace=True)
data_bili_1 = data_bili[(data_bili['FLAG'] == '品名')]
data_bili_1 = data_bili_1.reset_index(drop=True)
data_bili_1 = data_bili_1[['PROD_CODE', 'UL', 'LL']]
v = ['PROD_CODE']
df3 = pd.merge(data_mark, data_bili_1, on=v, how='left')
for index, row in df3.iterrows():
    exec('ul_{} ={}'.format(row['mark'], row['UL']))
    exec('ll_{} ={}'.format(row['mark'], row['LL']))
#分配比例上下限，按照性状的
for i in range(0, len(var_list)):
    print(var_list[i])
    tmp_var = var_list[i]
    data_bili_2 = data_bili[(data_bili['FLAG'] == '性状') & (data_bili['VAR'] == tmp_var)]
    data_bili_2 = data_bili_2.reset_index(drop=True)
    tmp_ul = data_bili_2.loc[0]['UL']
    tmp_ll = data_bili_2.loc[0]['LL']
    exec('ul_var_{} ={}'.format(i+1, tmp_ul))
    exec('ll_var_{} ={}'.format(i+1, tmp_ll))
#分配比例上下限，按照来源的
for i in range(0, len(source_list)):
    print(source_list[i])
    tmp_source = source_list[i]
    data_bili_3 = data_bili[(data_bili['FLAG'] == '来源') & (data_bili['SOURCE'] == tmp_source)]
    data_bili_3 = data_bili_3.reset_index(drop=True)
    tmp_ul = data_bili_3.loc[0]['UL']
    tmp_ll = data_bili_3.loc[0]['LL']
    exec('ul_source_{} ={}'.format(i+1, tmp_ul))
    exec('ll_source_{} ={}'.format(i+1, tmp_ll))
#分配比例上下限，按照性状来源的

# var_df_1 = var_df[(var_df['count'] > 0)]
# var_df_1 = var_df_1.reset_index(drop=True)

var_df1 = var_df.copy()
def __cal_new_var(x):
    rst = str(x.VAR) + '_' + str(x.SOURCE)
    return rst
var_df1['NEW_VAR'] = var_df1.apply(lambda x: __cal_new_var(x), axis=1)

data_bili_4 = data_bili[(data_bili['FLAG'] == '性状来源')]
data_bili_4 = data_bili_4.reset_index(drop=True)
def __cal_new_var(x):
    rst = str(x.VAR) + '_' + str(x.SOURCE)
    return rst
data_bili_4['NEW_VAR'] = data_bili_4.apply(lambda x: __cal_new_var(x), axis=1)
data_bili_4 = data_bili_4[['NEW_VAR', 'UL', 'LL']]
v = ['NEW_VAR']
df3 = pd.merge(var_df1, data_bili_4, on=v, how='left')
for index, row in df3.iterrows():
    exec('ul_group_{} ={}'.format(row['group'], row['UL']))
    exec('ll_group_{} ={}'.format(row['group'], row['LL']))
#煤质
data_meizhi.rename(columns={'ASH': 'hui'}, inplace=True)
data_meizhi.rename(columns={'COKE_VM': 'huifa'}, inplace=True)
data_meizhi.rename(columns={'S': 'liu'}, inplace=True)
data_meizhi_1 = data_meizhi[['PROD_CODE', 'hui', 'huifa', 'liu']]
v = ['PROD_CODE']
df3 = pd.merge(data_mark, data_meizhi_1, on=v, how='left')
df3.fillna(max_constant, inplace=True)
for index, row in df3.iterrows():
    exec('hui_{} ={}'.format(row['mark'], row['hui']))
    exec('huifa_{} ={}'.format(row['mark'], row['huifa']))
    exec('liu_{} ={}'.format(row['mark'], row['liu']))
#煤质上下限
data_shangxiaxian_1 = data_shangxiaxian[
    (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(max_constant, inplace=True)
lianjiao_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
lianjiao_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
lianjiao_liu_ul = data_shangxiaxian_1.loc[0]['S']
data_shangxiaxian_2 = data_shangxiaxian[
    (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
data_shangxiaxian_2 = data_shangxiaxian_2.reset_index(drop=True)
data_shangxiaxian_2.fillna(0, inplace=True)
lianjiao_hui_ll = data_shangxiaxian_2.loc[0]['ASH']
lianjiao_huifa_ll = data_shangxiaxian_2.loc[0]['COKE_VM']
lianjiao_liu_ll = data_shangxiaxian_2.loc[0]['S']

data_canshu = pd.read_excel('炼焦参数表.xlsx')
data_canshu.columns = data_canshu.columns.str.upper()
chengjiaolv_constant = data_canshu.loc[0]['COKEYR_CONST']
cogfasheng_constant = data_canshu.loc[0]['COG_GEN_CONST']
cujiaolv = data_canshu.loc[0]['SINTER_CRSCOKE_RATIO']
yejinjiaolv = data_canshu.loc[0]['COKING_METCOKRAT']
waigoujiaofenlv = data_canshu.loc[0]['PCOKE_COKEPOWD_RATE']
cogfadian = data_canshu.loc[0]['ELECOUT']
fadianmeihao = data_canshu.loc[0]['COAL_USE']
jiaotanchanliang = data_canshu.loc[0]['COKE_OUTPUT']
price_waigoudian = data_canshu.loc[0]['POWOUTSRC_PRICE']
price_tianranqi = data_canshu.loc[0]['NG_PRICE']
price_fadianmei = data_canshu.loc[0]['THMCOAL_PRICE']
data_sourceprice = pd.read_excel('炼焦资源价格表.xlsx')
data_sourceprice.columns = data_sourceprice.columns.str.upper()
data_other1 = data_sourceprice[data_sourceprice['PROD_DSCR']=='外购焦炭']
data_other1 = data_other1.reset_index(drop=True)
price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
data_other2 = data_sourceprice[data_sourceprice['PROD_DSCR']=='烧结燃料']
data_other2 = data_other2.reset_index(drop=True)
price_shaojieranliao = data_other2.loc[0]['UNIT_PRICE']
data_other3 = data_sourceprice[data_sourceprice['PROD_DSCR']=='COG']
data_other3 = data_other3.reset_index(drop=True)
price_cog = data_other3.loc[0]['UNIT_PRICE']
#其他配置的参数
#价格
price_waigoujiaotan = price_waigoujiaotan
price_waigoujiaofen = price_shaojieranliao
# price_tianranqi = 2.7
# price_waigoudian = 0.72
# price_fadianmei = 807.0
# price_cog = 1.39
#一些参数
# cujiaolv = 0.115
# yejinjiaolv = 0.835
waigoujiao_yejinjiaolv = 1 - waigoujiaofenlv
waigoujiao_cujiaolv = waigoujiaofenlv
COGfadian = cogfadian
# fadianmeihao = 420
# ganmeiliang = 67
# peihemei_wt = ganmeiliang

data_jieguotongji = pd.read_excel('炼焦结果统计表.xlsx')
data_jieguotongji.columns = data_jieguotongji.columns.str.upper()
data_month = data_jieguotongji[data_jieguotongji['SCHEME_NAME']=='月预算']
data_month = data_month.reset_index(drop=True)
month_unit_price = data_month.loc[0]['UNIT_PRICE']
month_ash = data_month.loc[0]['ASH']
month_coke_vm = data_month.loc[0]['COKE_VM']
month_s = data_month.loc[0]['S']
month_chengjiaolv = data_month.loc[0]['COKING_COKEYR']
month_jiaotanchanliang = data_month.loc[0]['COKE_OUTPUT']
month_cogfasheng = data_month.loc[0]['COG_GEN']
ganmeiliang = month_jiaotanchanliang / month_chengjiaolv * 100

#构建系数矩阵
coef_df = data_mark[['mark']]
mark_list = coef_df['mark'].to_list()
array_len = len(mark_list)
#构建所有参数对应的约束df
canshu_df = pd.DataFrame(columns=['index', 'coef_name', 'step', 'mark', 'vvar', 'source', 'group', 'meizhi','merge_tag'])
dict_canshu = {}
j_start = 0
#炼焦煤总量,暂认为干煤量
#等式约束
import numpy as np
from numpy import array
e2 = array([[1] * array_len], dtype=float)
f2 = array([peihemei_wt])
#不等式约束
#资源量
#品名资源量
for j in range(j_start, j_start + int(array_len)):
    exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
    exec('y{}[0,j-j_start] = 1'.format(j))
    mark_tmp = mark_list[j]
    exec('m{} = array([ziyuanliang_{}])'.format(j, mark_tmp))
    dict_canshu['index'] = j
    dict_canshu['coef_name'] = '资源量'
    if mark_tmp[0] == '1':
        buzhou_tmp = '主焦'
    elif mark_tmp[0] == '2':
        buzhou_tmp = '肥煤'
    elif mark_tmp[0] == '3':
        buzhou_tmp = '1/3焦'
    elif mark_tmp[0] == '4':
        buzhou_tmp = '气煤'
    elif mark_tmp[0] == '5':
        buzhou_tmp = '瘦煤'
    dict_canshu['step'] = buzhou_tmp
    dict_canshu['mark'] = mark_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
j_start = j_start + int(array_len)
#合并单元格资源量

for j in range(j_start, int(merge_num)+j_start):
    print(j)
    exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
    data_mark_merge = data_mark[(data_mark['MERGE_TAG'] == j-j_start+1)]
    data_mark_merge = data_mark_merge.reset_index(drop=True)
    var_tmp = data_mark_merge.loc[0]['VAR']

    data_mark_merge['if_merge'] = 1
    yuechukucun_sum = data_mark_merge['INIT_INV'].sum()
    data_mark_merge = data_mark_merge[['mark', 'if_merge']]
    v = ['mark']
    df3_merge = pd.merge(coef_df, data_mark_merge, on=v, how='left')
    df3_merge.if_merge.fillna(0, inplace=True)
    for index, row in df3_merge.iterrows():
        exec('y{}[0,index] = row["if_merge"]'.format(j))
    exec('m{} = array([merge_ziyuanliang_{}+yuechukucun_sum])'.format(j, j-j_start+1))

    dict_canshu['index'] = j
    dict_canshu['coef_name'] = '合并可供资源量'
    dict_canshu['step'] = '合并可供资源量'
    dict_canshu['merge_tag'] = j -j_start + 1
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}

j_start = j_start + int(merge_num)
#分配比例
#性状分配比例
ldict1 = {}
ldict2 = {}
for i in range(1,len(var_list)+1):
    var_tmp = str(i)
    #上限
    coef_df1 = coef_df.copy()
    exec('true_value = 100 - ul_var_{}'.format(var_tmp), locals(), ldict1)
    true_value = ldict1["true_value"]
    # print(true_value)
    exec('false_value = - ul_var_{}'.format(var_tmp), locals(), ldict1)
    false_value = ldict1["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark[0] == var_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '性状比例上限'
    dict_canshu['step'] = '维度比例上下限'
    dict_canshu['vvar'] = var_list[i-1]
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    #下限
    coef_df1 = coef_df.copy()
    exec('true_value = ll_var_{} - 100'.format(var_tmp), locals(), ldict2)
    true_value = ldict2["true_value"]
    # print(true_value)
    exec('false_value = ll_var_{}'.format(var_tmp), locals(), ldict2)
    false_value = ldict2["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark[0] == var_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '性状比例下限'
    dict_canshu['step'] = '维度比例上下限'
    dict_canshu['vvar'] = var_list[i - 1]
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#来源分配比例
ldict3 = {}
ldict4 = {}
for i in range(1,len(source_list)+1):
    source_tmp = str(i)
    #上限
    coef_df1 = coef_df.copy()
    exec('true_value = 100 - ul_source_{}'.format(source_tmp), locals(), ldict3)
    true_value = ldict3["true_value"]
    # print(true_value)
    exec('false_value = - ul_source_{}'.format(source_tmp), locals(), ldict3)
    false_value = ldict3["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark[2] == source_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '来源比例上限'
    dict_canshu['step'] = '维度比例上下限'
    dict_canshu['source'] = source_list[i-1]
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    #下限
    coef_df1 = coef_df.copy()
    exec('true_value = ll_source_{} - 100'.format(source_tmp), locals(), ldict4)
    true_value = ldict4["true_value"]
    # print(true_value)
    exec('false_value = ll_source_{}'.format(source_tmp), locals(), ldict4)
    false_value = ldict4["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark[2] == source_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '来源比例下限'
    dict_canshu['step'] = '维度比例上下限'
    dict_canshu['source'] = source_list[i-1]
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#性状来源分配比例
group_list = var_df['group'].to_list()
ldict5 = {}
ldict6 = {}
for group_tmp in group_list:
    # 上限
    coef_df1 = coef_df.copy()
    exec('true_value = 100 - ul_group_{}'.format(group_tmp), locals(), ldict5)
    true_value = ldict5["true_value"]
    # print(true_value)
    exec('false_value = - ul_group_{}'.format(group_tmp), locals(), ldict5)
    false_value = ldict5["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark[0:3] == group_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '性状来源比例上限'
    dict_canshu['step'] = '维度比例上下限'
    if group_tmp[0] == '1':
        var_tmp = '主焦'
    elif group_tmp[0] == '2':
        var_tmp = '肥煤'
    elif group_tmp[0] == '3':
        var_tmp = '1/3焦'
    elif group_tmp[0] == '4':
        var_tmp = '气煤'
    elif group_tmp[0] == '5':
        var_tmp = '瘦煤'
    dict_canshu['vvar'] = var_tmp
    if group_tmp[2] == '1':
        source_tmp = '进口'
    elif group_tmp[2] == '2':
        source_tmp = '大矿长协'
    elif group_tmp[2] == '3':
        source_tmp = '大矿市场'
    elif group_tmp[2] == '4':
        source_tmp = '地方矿'
    dict_canshu['source'] = source_tmp
    dict_canshu['group'] = group_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    # 下限
    coef_df1 = coef_df.copy()
    exec('true_value = ll_group_{} - 100'.format(group_tmp), locals(), ldict6)
    true_value = ldict6["true_value"]
    # print(true_value)
    exec('false_value = ll_group_{}'.format(group_tmp), locals(), ldict6)
    false_value = ldict6["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark[0:3] == group_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '性状来源比例下限'
    dict_canshu['step'] = '维度比例上下限'
    if group_tmp[0] == '1':
        var_tmp = '主焦'
    elif group_tmp[0] == '2':
        var_tmp = '肥煤'
    elif group_tmp[0] == '3':
        var_tmp = '1/3焦'
    elif group_tmp[0] == '4':
        var_tmp = '气煤'
    elif group_tmp[0] == '5':
        var_tmp = '瘦煤'
    dict_canshu['vvar'] = var_tmp
    if group_tmp[2] == '1':
        source_tmp = '进口'
    elif group_tmp[2] == '2':
        source_tmp = '大矿长协'
    elif group_tmp[2] == '3':
        source_tmp = '大矿市场'
    elif group_tmp[2] == '4':
        source_tmp = '地方矿'
    dict_canshu['source'] = source_tmp
    dict_canshu['group'] = group_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#品名分配比例
ldict7 = {}
ldict8 = {}
for mark_tmp in mark_list:
    # 上限
    coef_df1 = coef_df.copy()
    exec('true_value = 100 - ul_{}'.format(mark_tmp), locals(), ldict7)
    true_value = ldict7["true_value"]
    # print(true_value)
    exec('false_value = - ul_{}'.format(mark_tmp), locals(), ldict7)
    false_value = ldict7["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark == mark_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '品种比例上限'
    if mark_tmp[0] == '1':
        buzhou_tmp = '主焦'
    elif mark_tmp[0] == '2':
        buzhou_tmp = '肥煤'
    elif mark_tmp[0] == '3':
        buzhou_tmp = '1/3焦'
    elif mark_tmp[0] == '4':
        buzhou_tmp = '气煤'
    elif mark_tmp[0] == '5':
        buzhou_tmp = '瘦煤'
    dict_canshu['step'] = buzhou_tmp
    dict_canshu['mark'] = mark_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    # 下限
    coef_df1 = coef_df.copy()
    exec('true_value = ll_{} - 100'.format(mark_tmp), locals(), ldict8)
    true_value = ldict8["true_value"]
    # print(true_value)
    exec('false_value = ll_{}'.format(mark_tmp), locals(), ldict8)
    false_value = ldict8["false_value"]
    # print(false_value)
    def __cal_coef(x):
        if x.mark == mark_tmp:
            rst = true_value
        else:
            rst = false_value
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '品种比例下限'
    if mark_tmp[0] == '1':
        buzhou_tmp = '主焦'
    elif mark_tmp[0] == '2':
        buzhou_tmp = '肥煤'
    elif mark_tmp[0] == '3':
        buzhou_tmp = '1/3焦'
    elif mark_tmp[0] == '4':
        buzhou_tmp = '气煤'
    elif mark_tmp[0] == '5':
        buzhou_tmp = '瘦煤'
    dict_canshu['step'] = buzhou_tmp
    dict_canshu['mark'] = mark_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#煤质
meizhi_list = ['hui', 'huifa', 'liu']
big_var_tmp = 'lianjiao'
for meizhi_tmp in meizhi_list:
    coef_df1 = coef_df.copy()
    # 上限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                       meizhi_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '上限'
    dict_canshu['step'] = '煤质上下限'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    # 下限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                       mark_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['index'] = j_start
    dict_canshu['coef_name'] = '下限'
    dict_canshu['step'] = '煤质上下限'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1

# 拼接
ldict0 = {}
y_str = 'y0'
# yyy = (y0,y0)
for j in range(1, j_start):
    y_str = y_str + ',' + 'y' + str(j)
# exec('yyy =({})'.format(y_str))
exec('yyy =({})'.format(y_str), locals(), ldict0)
yyy = ldict0["yyy"]
Y = np.concatenate(yyy, axis=0)

m_str = 'm0'
# mmm = (m0,m0)
for j in range(1, j_start):
    m_str = m_str + ',' + 'm' + str(j)
# exec('mmm =({})'.format(m_str))
exec('mmm =({})'.format(m_str), locals(), ldict0)
mmm = ldict0["mmm"]
M = np.concatenate(mmm, axis=0)
# print('finish')
#计划
data_mark_plan = data_mark[(data_mark['PLAN_WT'] > 0)]
data_mark_plan = data_mark_plan.reset_index(drop=True)
# mark_plan_list = data_mark_plan['mark'].to_list()
v = ['PROD_CODE']
df4 = pd.merge(data_mark_plan, data_meizhi_1, on=v, how='left')
df4['UNIT_PRICE'] = 1
df4['TOTAL_PRICE'] = df4['UNIT_PRICE'] * df4['PLAN_WT']
df4['TOTAL_VV'] = df4['huifa'] * df4['PLAN_WT']
plan_peihemei_total_price = df4['TOTAL_PRICE'].sum()
plan_peihemei_unit_price = plan_peihemei_total_price / peihemei_wt
plan_peihemei_huifa = df4['TOTAL_VV'].sum() / peihemei_wt
plan_lilunchengjiaolv = 97 - plan_peihemei_huifa * 5 / 6 + chengjiaolv_constant
plan_jiaotanchanliang = peihemei_wt * plan_lilunchengjiaolv / 100
plan_COGfashengliang = (9.37 * plan_peihemei_huifa + 66.7) * peihemei_wt / 24 / 30 + cogfasheng_constant
#调整
#价格
#cc假定价格为1
cc = array([[1] * array_len], dtype=float)
#挥发分
vv_str = ''
ldict9 = {}
for i in range(0, len(mark_list)):
    mark_tmp = mark_list[i]
    if i == 0:
        exec("vv_str = vv_str + 'huifa_{}'".format(mark_tmp), locals(), ldict9)
        vv_str = ldict9["vv_str"]
    else:
        exec("vv_str = vv_str + ',' + 'huifa_{}'".format(mark_tmp), locals(), ldict9)
        vv_str = ldict9["vv_str"]
        # print(vv_str)
exec("vv = array([{}])".format(vv_str), locals(), ldict0)
vv = ldict0["vv"]
#判断函数
def judge_solution_exist(e1, f1):
    """
    传入e1，f1进行线性规划求解，判断是否有可行域
    """
    # cujiaolv = 0.115
    # yejinjiaolv = 0.835
    # waigoujiao_yejinjiaolv = 0.8
    # waigoujiao_cujiaolv = 0.2
    # COGfadian = 1.9
    # fadianmeihao = 420
    x = cp.Variable(array_len)
    peihemei_total_price = cc @ x
    peihemei_unit_price = peihemei_total_price / peihemei_wt
    peihemei_huifa = (vv @ x) / peihemei_wt
    lilunchengjiaolv = 97 - peihemei_huifa * 5 / 6 + chengjiaolv_constant
    jiaotanchanliang = peihemei_wt * lilunchengjiaolv / 100
    COGfashengliang = (9.37 * peihemei_huifa + 66.7) * peihemei_wt / 24 / 30 + cogfasheng_constant
    delta_jiaotanchanliang = jiaotanchanliang - plan_jiaotanchanliang

    delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
    delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / waigoujiao_yejinjiaolv
    delta_cujiaocaigou = delta_waigoujiao * waigoujiao_cujiaolv - delta_culiaoliang
    delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
    delta_cujiaocaigou_cost = delta_cujiaocaigou * price_waigoujiaofen
    # delta_peihemei_cost = peihemei_total_price - plan_peihemei_total_price
    delta_peihemei_cost = (peihemei_unit_price - plan_peihemei_unit_price)* ganmeiliang

    delta_COGfashengliang = (plan_COGfashengliang - COGfashengliang) * 30 * 24
    # COG_DEST焦炉煤气去向
    # 发电；补充焦炉煤气


    if cog_dest != '发电':
        delta_COG_cost = delta_COGfashengliang * price_cog
    # elif cog_dest=='发电':
    else:
        delta_COG_fadian = delta_COGfashengliang * COGfadian
        tianranqifadian = delta_COGfashengliang / 2
        fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
        waigoudian = delta_COG_fadian
        # 都是delta_COGfashengliang的倍数，比较倍数取最小的
        tianranqi_coef = 0.5 * price_tianranqi
        fadianmei_coef = COGfadian * fadianmeihao / 100 * price_tianranqi /10000
        waigoudian_coef = COGfadian * price_waigoudian
        delta_COG_cost1 = tianranqifadian * price_tianranqi
        delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
        delta_COG_cost3 = waigoudian * price_waigoudian
        # delta_COG_cost = min(delta_COG_cost1, delta_COG_cost2, delta_COG_cost3)
        # # 发电三种代替方式，取最小的作为目标函数
        if tianranqi_coef <= fadianmei_coef and tianranqi_coef <= waigoudian_coef:
            delta_COG_cost = delta_COG_cost1
        elif fadianmei_coef <= tianranqi_coef and fadianmei_coef <= waigoudian_coef:
            delta_COG_cost = delta_COG_cost2
        elif waigoudian_coef <= tianranqi_coef and waigoudian_coef <= fadianmei_coef:
            delta_COG_cost = delta_COG_cost3
    total_cost = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost

    obj = cp.Minimize(total_cost)
    cons = [e1 @ x <= f1, e2 @ x == f2, x >= 0]
    prob = cp.Problem(obj, cons)
    prob.solve(solver='GLPK_MI', verbose=True)
    # print("最优初始值为:", prob.value)
    # print("最优初始解为：\n", x.value)
    success = 0
    if x.value is None:
        success = 0
    else:
        success = 1
    # print(success)
    return success

success = judge_solution_exist(e1=Y, f1=M)
yy = y0
mm = m0
listb = []
if success == 1:
    message = '约束条件合理，存在最优解'
    result_list = []
    # print(message)
else:
    message = '约束条件不合理，不存在最优解'
    # print(message)
    # print('无解，开始循环找需要修改的约束条件')
    for i in range(1, j_start):
        yi = Y[[i]]
        mi = M[[i]]
        Yy = np.concatenate((yy, yi), axis=0)
        Mm = np.concatenate((mm, mi), axis=0)
        success = judge_solution_exist(e1=Yy, f1=Mm)
        if success == 1:
            yy = Yy
            mm = Mm
        else:
            yy = yy
            mm = mm
            # print(i)
            listb.append(i)
            # print(listb)
    df2 = canshu_df.iloc[listb]
    # print('需要修改的约束条件参数为')
    # print(df2)
# df2 = canshu_df

df2 = df2.reset_index(drop=True)
data_mark_tmp = data_mark[['VAR', 'SOURCE', 'PROD_DSCR', 'mark']]
data_mark_tmp.rename(columns={'VAR': 'VAR_TMP'}, inplace=True)
data_mark_tmp.rename(columns={'SOURCE': 'SOURCE_TMP'}, inplace=True)
data_mark_tmp.rename(columns={'PROD_DSCR': 'PROD_DSCR_TMP'}, inplace=True)

v = ['mark']
df2 = pd.merge(df2, data_mark_tmp, on=v, how='left')
# var_df_tmp = var_df[['group', 'VAR', 'SOURCE']]
# var_df_tmp.rename(columns={'SOURCE': 'SOURCE_TMP_group'}, inplace=True)
# var_df_tmp.rename(columns={'VAR': 'VAR_TMP_group'}, inplace=True)
# v = ['group']
# df2 = pd.merge(df2, var_df_tmp, on=v, how='left')


def __cal_coef(x):
    if x.step == '煤质上下限':
        ind = ''
        if x.meizhi == 'hui':
            ind = '灰分'
        elif x.meizhi == 'huifa':
            ind = '挥发分'
        elif x.meizhi == 'liu':
            ind = '硫分'
        rst = '炼焦煤' + '_' + ind + '_' + str(x.coef_name)
    elif x.coef_name == '品种比例上限' or x.coef_name == '品种比例下限':
        rst = str(x.VAR_TMP) + '_' + str(x.SOURCE_TMP) + '_' + str(x.PROD_DSCR_TMP) + '_' + str(x.coef_name)
    elif x.coef_name == '性状比例上限' or x.coef_name == '性状比例下限':
        rst = str(x.vvar) + '_' + str(x.coef_name)
    elif x.coef_name == '来源比例上限' or x.coef_name == '来源比例下限':
        rst = str(x.source) + '_' + str(x.coef_name)
    elif x.coef_name == '性状来源比例上限' or x.coef_name == '性状来源比例下限':
        rst = str(x.vvar) + '_' + str(x.source) + '_' + str(x.coef_name)
    elif x.coef_name == '资源量':
        rst = str(x.VAR_TMP) + '_' + str(x.SOURCE_TMP) + '_' + str(x.PROD_DSCR_TMP) + '_' + str(x.coef_name)
    elif x.coef_name == '合并可供资源量':
        rst = '第' + str(x.merge_tag) + '组' + '_' + str(x.coef_name)
    else:
        rst = x.coef_name
    return rst
df2['new_coef'] = df2.apply(lambda x: __cal_coef(x), axis=1)
def __cal_coef_col_name(x):
    if x.step == '煤质上下限':
        if x.meizhi == 'hui':
            rst = 'ASH'
        elif x.meizhi == 'huifa':
            rst = 'COKE_VM'
        elif x.meizhi == 'liu':
            rst = 'S'
    elif x.coef_name == '品种比例上限':
        rst = 'MAX_VALUE'
    elif x.coef_name == '品种比例下限':
        rst = 'MIN_VALUE'
    elif x.coef_name == '性状比例上限':
        rst = 'MAX_VALUE'
    elif x.coef_name == '性状比例下限':
        rst = 'MIN_VALUE'
    elif x.coef_name == '来源比例上限':
        rst = 'MAX_VALUE'
    elif x.coef_name == '来源比例下限':
        rst = 'MIN_VALUE'
    elif x.coef_name == '性状来源比例上限':
        rst = 'MAX_VALUE'
    elif x.coef_name == '性状来源比例下限':
        rst = 'MIN_VALUE'
    elif x.coef_name == '资源量':
        rst = 'RESOURCE_WT'
    elif x.coef_name == '合并可供资源量':
        rst = 'INV_WT'
    return rst
df2['new_coef_col_name'] = df2.apply(lambda x: __cal_coef_col_name(x), axis=1)
def __cal_coef_index_name(x):
    if x.coef_name == '上限':
        rst = '上限'
    elif x.coef_name == '下限':
        rst = '下限'
    elif x.coef_name == '品种比例上限' or x.coef_name == '品种比例下限':
        rst = x.PROD_DSCR_TMP
    elif x.coef_name == '性状比例上限' or x.coef_name == '性状比例下限':
        rst = x.vvar
    elif x.coef_name == '来源比例上限' or x.coef_name == '来源比例下限':
        rst = x.source
    elif x.coef_name == '性状来源比例上限' or x.coef_name == '性状来源比例下限':
        rst = str(x.vvar) + '_' + str(x.source)
    elif x.coef_name == '资源量':
        rst = x.PROD_DSCR_TMP
    elif x.coef_name == '合并可供资源量':
        rst = x.merge_tag
    return rst
df2['new_coef_index_name'] = df2.apply(lambda x: __cal_coef_index_name(x), axis=1)
# df_out = pd.DataFrame(columns=['COEF_NAME', 'STEP_NAME', 'TABLE_NAME', 'INDEX_NUM', 'COL_NUM'])
# dict = {}
# message = message + '需要修改的约束条件参数为：'
# for index, row in df2.iterrows():
#     dict['COEF_NAME'] = row['new_coef']
#     message = message + row['new_coef'] + '；'
#     dict['STEP_NAME'] = row['step']
#     if row['coef_name'] == '性状比例上限' or row['coef_name'] == '性状比例下限':
#         dict['TABLE_NAME'] = '表1'
#     elif row['coef_name'] == '来源比例上限' or row['coef_name'] == '来源比例下限':
#         dict['TABLE_NAME'] = '表2'
#     elif row['coef_name'] == '性状来源比例上限' or row['coef_name'] == '性状来源比例下限':
#         dict['TABLE_NAME'] = '表3'
#     elif row['coef_name'] == '品种比例上限' or row['coef_name'] == '品种比例下限':
#         dict['TABLE_NAME'] = '表1'
#     elif row['coef_name'] == '资源量':
#         dict['TABLE_NAME'] = '表1'
#     else:
#         dict['TABLE_NAME'] = ''
#     if row['step'] == '煤质上下限':
#         sql = ""
#         data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
#         data_tmp.columns = data_tmp.columns.str.upper()
#         flag_tmp = row['coef_name']
#         ind_tmp = row['new_coef_col_name']
#         row_index = data_tmp.loc[(data_tmp['FLAG'] == flag_tmp)].index[0]
#         column_index = data_tmp.columns.get_loc(ind_tmp)
#         dict['INDEX_NUM'] = row_index
#         dict['COL_NUM'] = column_index
#     elif row['coef_name'] == '性状比例上限' or row['coef_name'] == '性状比例下限':
#         sql = ""
#         data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
#         data_tmp.columns = data_tmp.columns.str.upper()
#         var_tmp = row['vvar']
#         ind_tmp = row['new_coef_col_name']
#         row_index = data_tmp.loc[data_tmp['VAR'] == var_tmp].index[0]  # 找到满足条件的第一行的行索引
#         column_index = data_tmp.columns.get_loc(ind_tmp)
#         dict['INDEX_NUM'] = row_index
#         dict['COL_NUM'] = column_index
#     elif row['coef_name'] == '来源比例上限' or row['coef_name'] == '来源比例下限':
#         sql = ""
#         data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
#         data_tmp.columns = data_tmp.columns.str.upper()
#         source_tmp = row['source']
#         ind_tmp = row['new_coef_col_name']
#         row_index = data_tmp.loc[data_tmp['SOURCE'] == source_tmp].index[0]  # 找到满足条件的第一行的行索引
#         column_index = data_tmp.columns.get_loc(ind_tmp)
#         dict['INDEX_NUM'] = row_index
#         dict['COL_NUM'] = column_index
#     elif row['coef_name'] == '性状来源比例上限' or row['coef_name'] == '性状来源比例下限':
#         sql = ""
#         data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
#         data_tmp.columns = data_tmp.columns.str.upper()
#         var_tmp = row['vvar']
#         source_tmp = row['source']
#         ind_tmp = row['new_coef_col_name']
#         row_index = data_tmp.loc[(data_tmp['VAR'] == var_tmp) & (data_tmp['SOURCE'] == source_tmp)].index[0]  # 找到满足条件的第一行的行索引
#         column_index = data_tmp.columns.get_loc(ind_tmp)
#         dict['INDEX_NUM'] = row_index
#         dict['COL_NUM'] = column_index
#     elif row['coef_name'] == '品种比例上限' or row['coef_name'] == '品种比例下限' or row['参数名'] == '资源量':
#         if row['step'] == '主焦':
#             sql = ""
#         elif row['step'] == '肥煤':
#             sql = ""
#         elif row['step'] == '1/3焦':
#             sql = ""
#         elif row['step'] == '气煤':
#             sql = ""
#         elif row['step'] == '瘦煤':
#             sql = ""
#         data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
#         data_tmp.columns = data_tmp.columns.str.upper()
#         prod_dscr_tmp = row['PROD_DSCR']
#         ind_tmp = row['new_coef_col_name']
#         row_index = data_tmp.loc[data_tmp['PROD_DSCR'] == prod_dscr_tmp].index[0]  # 找到满足条件的第一行的行索引
#         column_index = data_tmp.columns.get_loc(ind_tmp)
#         dict['INDEX_NUM'] = row_index
#         dict['COL_NUM'] = column_index + 1
#     elif row['coef_name'] == '合并可供资源量':
#         sql = ""
#         data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
#         data_tmp.columns = data_tmp.columns.str.upper()
#         merge_tag_tmp = row['merge_tag']
#         ind_tmp = row['new_coef_col_name']
#         row_index = data_tmp.loc[data_tmp['merge_tag'] == merge_tag_tmp].index[0]  # 找到满足条件的第一行的行索引
#         column_index = data_tmp.columns.get_loc(ind_tmp)
#         dict['INDEX_NUM'] = row_index
#         dict['COL_NUM'] = column_index
#     new_row = pd.Series(dict)
#     df_out = df_out.append(new_row, ignore_index=True)
# result_list = df_out.to_dict(orient='records')


print('finish')





